﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using SARS.DAO.Facades;
using SARS.Entity;

namespace SARS.DAO
{
    public class SessionDAO : ISessionDAO
    {
        private readonly SqlConnection _conn = new SqlConnection(Utils.SarConnectionString);
        private SqlCommand _cmd;
        private SqlDataReader _dr;

        public List<SessionEntity> RetrieveAllSession()
        {
            var sessionEntityList = new List<SessionEntity>();

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("SELECT "
                                      + "s.[SessionID] "
                                      + ", s.[SessionTopic] "
                                      + ", s.[SessionType]"
                                      + ", s.[CourseID]"
                                      + ", s.[StartDate]"
                                      + ", s.[EndDate]"
                                      + ", c.[CourseName]"
                                      + ", u.[FirstName]"
                                      + ", u.[LastName]"
                                      + ", r.[RoomName]"
                                      + ", r.[RoomType]"
                                      + ", s.[Status]"
                                      + " FROM [dbo].[Sessions] s"
                                      + " JOIN [dbo].[Courses] c ON c.[CourseID] = s.[CourseID]"
                                      + " JOIN [dbo].[Users] u ON u.[UserID] = s.[ManagerID]"
                                      + " JOIN [dbo].[Rooms] r ON r.[RoomID] = s.[RoomID]", _conn);
                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    var userEntity = new UserEntity
                    {
                        FirstName = _dr["FirstName"].ToString(),
                        LastName = _dr["LastName"].ToString()
                    };

                    var courseEntity = new CourseEntity
                    {
                        CourseName = _dr["CourseName"].ToString()
                    };

                    var roomEntity = new RoomEntity
                    {
                        RoomName = _dr["RoomName"].ToString(),
                        RoomType = _dr["RoomType"].ToString()
                    };

                    var sessionEntity = new SessionEntity
                    {
                        SessionID = Int32.Parse(_dr["SessionID"].ToString()),
                        SessionTopic = _dr["SessionTopic"].ToString(),
                        SessionType = _dr["SessionType"].ToString(),
                        CourseID = Int32.Parse(_dr["CourseID"].ToString()),
                        StartDate = DateTime.Parse(_dr["StartDate"].ToString()),
                        EndDate = DateTime.Parse(_dr["EndDate"].ToString()),
                        Status = Int32.Parse(_dr["Status"].ToString()),
                        Manager = userEntity,
                        Course = courseEntity,
                        Room = roomEntity
                    };

                    sessionEntityList.Add(sessionEntity);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Session] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return sessionEntityList;
        }

        public SessionEntity RetrieveSessionById(int sessionId)
        {
            var sessionEntity = new SessionEntity();

            try
            {
                _conn.Open();

                _cmd =
                    new SqlCommand(
                        "SELECT * FROM [dbo].[Sessions] WHERE [SessionID] = @SessionID", _conn);
                _cmd.Parameters.AddWithValue("@SessionID", sessionId);

                _dr = _cmd.ExecuteReader(CommandBehavior.SingleRow);

                while (_dr.HasRows && _dr.Read())
                {
                    sessionEntity.SessionID = Int32.Parse(_dr["SessionID"].ToString());
                    sessionEntity.SessionTopic = _dr["SessionTopic"].ToString();
                    sessionEntity.SessionType = _dr["SessionType"].ToString();
                    sessionEntity.StartDate = DateTime.Parse(_dr["StartDate"].ToString());
                    sessionEntity.EndDate = DateTime.Parse(_dr["EndDate"].ToString());
                    sessionEntity.CourseID = Int32.Parse(_dr["CourseID"].ToString());
                    sessionEntity.ManagerID = Int32.Parse(_dr["ManagerID"].ToString());
                    sessionEntity.RoomID = Int32.Parse(_dr["RoomID"].ToString());
                    sessionEntity.CreatedDate = DateTime.Parse(_dr["CreatedDate"].ToString());
                    string modDate = _dr["ModifiedDate"].ToString();
                    if (!string.IsNullOrEmpty(modDate))
                    {
                        sessionEntity.ModifiedDate = DateTime.Parse(modDate);
                    }
                    sessionEntity.Status = Int32.Parse(_dr["Status"].ToString());
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Session] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return sessionEntity;
        }

        public int InsertSession(SessionEntity entity)
        {
            int result = 0;

            try
            {
                var id = new SqlParameter("id", 0) {Direction = ParameterDirection.Output};

                _conn.Open();

                _cmd = new SqlCommand("INSERT INTO [dbo].[Sessions]"
                                      + "([SessionTopic]"
                                      + ",[SessionType]"
                                      + ",[StartDate]"
                                      + ",[EndDate]"
                                      + ",[CourseID]"
                                      + ",[ManagerID]"
                                      + ",[RoomID]"
                                      + ",[CreatedDate]"
                                      + ",[ModifiedDate]"
                                      + ",[Status])"
                                      + "VALUES"
                                      + "(@SessionTopic"
                                      + ",@SessionType"
                                      + ",@StartDate"
                                      + ",@EndDate"
                                      + ",@CourseID"
                                      + ",@ManagerID"
                                      + ",@RoomID"
                                      + ",@CreatedDate"
                                      + ",@ModifiedDate"
                                      + ",@Status); SELECT @id=SCOPE_IDENTITY()", _conn);

                _cmd.Parameters.AddWithValue("@SessionTopic", entity.SessionTopic);
                _cmd.Parameters.AddWithValue("@SessionType", entity.SessionType);
                _cmd.Parameters.AddWithValue("@StartDate", entity.StartDate);
                _cmd.Parameters.AddWithValue("@EndDate", entity.EndDate);
                _cmd.Parameters.AddWithValue("@CourseID", entity.CourseID);
                _cmd.Parameters.AddWithValue("@ManagerID", entity.ManagerID);
                _cmd.Parameters.AddWithValue("@RoomID", entity.RoomID);
                _cmd.Parameters.AddWithValue("@CreatedDate", DateTime.Now);
                _cmd.Parameters.AddWithValue("@ModifiedDate", DBNull.Value);
                _cmd.Parameters.AddWithValue("@Status", entity.Status);

                _cmd.Parameters.Add(id);
                _cmd.ExecuteNonQuery();

                result = Convert.ToInt32(id.Value);

                // Cleanup command and connection objects.
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Session] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return result;
        }

        public List<SessionEntity> RetrieveSessionByCourseId(int courseId)
        {
            return RetrieveAllSession().Where(x => x.CourseID == courseId).ToList();
        }

        public int InsertAttendanceSession(List<AttendanceSessionEntity> entities)
        {
            int result = 0;

            try
            {
                _conn.Open();

                foreach (AttendanceSessionEntity entity in entities)
                {
                    _cmd =
                        new SqlCommand(
                            "INSERT INTO [dbo].[AttendanceSession]([SessionID],[StudentID],[IsAbsent],[Creator],[CreatedDate],[ModifiedDate],[ModifiedBy])"
                            + "VALUES(@SessionID,@StudentID,@IsAbsent,@Creator,@CreatedDate,@ModifiedDate,@ModifiedBy)",
                            _conn);

                    _cmd.Parameters.AddWithValue("@SessionID", entity.SessionID);
                    _cmd.Parameters.AddWithValue("@StudentID", entity.StudentID);
                    _cmd.Parameters.AddWithValue("@IsAbsent", entity.IsAbsent);
                    _cmd.Parameters.AddWithValue("@Creator", entity.Creator);
                    _cmd.Parameters.AddWithValue("@CreatedDate", entity.CreatedDate);
                    _cmd.Parameters.AddWithValue("@ModifiedDate", DBNull.Value);
                    _cmd.Parameters.AddWithValue("@ModifiedBy", DBNull.Value);

                    result += _cmd.ExecuteNonQuery();
                }

                // Cleanup command and connection objects.
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Session] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return result;
        }

        public List<AttendanceSessionEntity> RetrieveAttendanceSessionBySessionId(int sessionId)
        {
            var attendanceSessionEntities = new List<AttendanceSessionEntity>();

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("SELECT * FROM [dbo].[AttendanceSession] WHERE [SessionID] = @SessionID", _conn);
                _cmd.Parameters.AddWithValue("@SessionID", sessionId);

                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    var attendanceSessionEntity = new AttendanceSessionEntity
                    {
                        ID = Int32.Parse(_dr["ID"].ToString()),
                        SessionID = Int32.Parse(_dr["SessionID"].ToString()),
                        StudentID = Int32.Parse(_dr["StudentID"].ToString()),
                        IsAbsent = Int32.Parse(_dr["IsAbsent"].ToString()),
                        Creator = Int32.Parse(_dr["Creator"].ToString()),
                        CreatedDate = Convert.ToDateTime(_dr["CreatedDate"].ToString()),
                        //ModifiedDate = _dr["ModifiedDate"] != null ? Convert.ToDateTime(_dr["ModifiedDate"].ToString()) : DateTime.MinValue,
                        //ModifiedBy = Int32.Parse(_dr["ModifiedBy"].ToString())
                    };

                    attendanceSessionEntities.Add(attendanceSessionEntity);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Session] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return attendanceSessionEntities;
        }

        public int UpdateSessionById(SessionEntity entity)
        {
            int result = 0;

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("UPDATE [dbo].[Sessions]"
                                      + " SET [SessionTopic] = @SessionTopic"
                                      + " ,[SessionType] = @SessionType"
                                      + " ,[StartDate] = @StartDate"
                                      + " ,[EndDate] = @EndDate"
                                      + " ,[CourseID] = @CourseID"
                                      + " ,[ManagerID] = @ManagerID"
                                      + " ,[RoomID] = @RoomID"
                                      + " ,[ModifiedDate] = @ModifiedDate"
                                      + " ,[Status] = @Status"
                                      + " WHERE [SessionID] = @SessionID", _conn);

                _cmd.Parameters.AddWithValue("@SessionID", entity.SessionID);
                _cmd.Parameters.AddWithValue("@SessionTopic", entity.SessionTopic);
                _cmd.Parameters.AddWithValue("@SessionType", entity.SessionType);
                _cmd.Parameters.AddWithValue("@StartDate", entity.StartDate);
                _cmd.Parameters.AddWithValue("@EndDate", entity.EndDate);
                _cmd.Parameters.AddWithValue("@CourseID", entity.CourseID);
                _cmd.Parameters.AddWithValue("@ManagerID", entity.ManagerID);
                _cmd.Parameters.AddWithValue("@RoomID", entity.RoomID);
                _cmd.Parameters.AddWithValue("@ModifiedDate", DateTime.Now);
                _cmd.Parameters.AddWithValue("@Status", entity.Status);

                result = _cmd.ExecuteNonQuery();

                // Cleanup command and connection objects.
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Session] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return result;
        }

        public int DeleteSessionById(int id)
        {
            int result = 0;

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("DELETE FROM [dbo].[Sessions] WHERE [SessionID] = @SessionID", _conn);

                _cmd.Parameters.AddWithValue("@SessionID", id);

                result = _cmd.ExecuteNonQuery();

                // Cleanup command and connection objects.
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Session] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return result;
        }

        public int UpdateAttendanceSessionById(AttendanceSessionEntity entity)
        {
            int result = 0;

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("UPDATE [dbo].[AttendanceSession]"
                                      + " SET [ModifiedDate] = @ModifiedDate"
                                      + ", [ModifiedBy] = @ModifiedBy"
                                      + ", [IsAbsent] = @IsAbsent"
                                      + " WHERE [SessionID] = @SessionID AND [StudentID] = @StudentID", _conn);

                _cmd.Parameters.AddWithValue("@ModifiedDate", entity.ModifiedDate);
                _cmd.Parameters.AddWithValue("@ModifiedBy", entity.ModifiedBy);
                _cmd.Parameters.AddWithValue("@IsAbsent", entity.IsAbsent);
                _cmd.Parameters.AddWithValue("@SessionID", entity.SessionID);
                _cmd.Parameters.AddWithValue("@StudentID", entity.StudentID);

                result = _cmd.ExecuteNonQuery();

                // Cleanup command and connection objects.
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Session] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return result;
        }

        public int DeleteAttendanceSessionById(int id)
        {
            int result = 0;

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("DELETE FROM [dbo].[AttendanceSession] WHERE [SessionID] = @SessionID", _conn);

                _cmd.Parameters.AddWithValue("@SessionID", id);

                result = _cmd.ExecuteNonQuery();

                // Cleanup command and connection objects.
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Session] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return result;
        }

        public List<AttendanceSessionEntity> RetrieveAttendanceSessionAll()
        {
            var attendanceSessionEntities = new List<AttendanceSessionEntity>();

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("SELECT * FROM [dbo].[AttendanceSession]", _conn);
                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    var attendanceSessionEntity = new AttendanceSessionEntity
                    {
                        ID = Int32.Parse(_dr["ID"].ToString()),
                        SessionID = Int32.Parse(_dr["SessionID"].ToString()),
                        StudentID = Int32.Parse(_dr["StudentID"].ToString()),
                        IsAbsent = Int32.Parse(_dr["IsAbsent"].ToString()),
                        Creator = Int32.Parse(_dr["Creator"].ToString()),
                        CreatedDate = Convert.ToDateTime(_dr["CreatedDate"].ToString()),
                        //ModifiedDate = _dr["ModifiedDate"] != null ? Convert.ToDateTime(_dr["ModifiedDate"].ToString()) : DateTime.MinValue,
                        //ModifiedBy = Int32.Parse(_dr["ModifiedBy"].ToString())
                    };

                    attendanceSessionEntities.Add(attendanceSessionEntity);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Session] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return attendanceSessionEntities;
        }

        public List<AttendanceSessionEntity> GetAttendanceSessionByCourseIdAndStudentId(int courseId, int studentId)
        {
            var attendanceSessionEntities = new List<AttendanceSessionEntity>();

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("SELECT * from [Sessions] AS S JOIN [AttendanceSession] AS AT " +
                                      "ON S.SessionID = AT.SessionID WHERE S.CourseID = @courseId AND AT.StudentID = @studentId",
                    _conn);
                _cmd.Parameters.Add(new SqlParameter("@courseId", courseId));
                _cmd.Parameters.Add(new SqlParameter("@studentId", studentId));

                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    var attendanceSessionEntity = new AttendanceSessionEntity
                    {
                        ID = Int32.Parse(_dr["ID"].ToString()),
                        SessionID = Int32.Parse(_dr["SessionID"].ToString()),
                        StudentID = Int32.Parse(_dr["StudentID"].ToString()),
                        IsAbsent = Int32.Parse(_dr["IsAbsent"].ToString()),
                        Creator = Int32.Parse(_dr["Creator"].ToString()),
                        CreatedDate = Convert.ToDateTime(_dr["CreatedDate"].ToString()),
                    };

                    attendanceSessionEntities.Add(attendanceSessionEntity);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Session] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return attendanceSessionEntities;
        }

        public List<SessionEntity> GetSessionsByManagerId(int managerId)
        {
            return RetrieveAllSession().Where(x => x.ManagerID == managerId).ToList();
        }

        public SessionEntity GetSessionById(int sessionId)
        {
            var sessionEntity = new SessionEntity();

            try
            {
                _conn.Open();

                _cmd =
                    new SqlCommand(
                        "SELECT * FROM [dbo].[Sessions] WHERE [SessionID] = @SessionID", _conn);
                _cmd.Parameters.AddWithValue("@SessionID", sessionId);

                _dr = _cmd.ExecuteReader(CommandBehavior.SingleRow);

                while (_dr.HasRows && _dr.Read())
                {
                    sessionEntity.SessionID = Int32.Parse(_dr["SessionID"].ToString());
                    sessionEntity.SessionTopic = _dr["SessionTopic"].ToString();
                    sessionEntity.SessionType = _dr["SessionType"].ToString();
                    sessionEntity.StartDate = DateTime.Parse(_dr["StartDate"].ToString());
                    sessionEntity.EndDate = DateTime.Parse(_dr["EndDate"].ToString());
                    sessionEntity.CourseID = Int32.Parse(_dr["CourseID"].ToString());
                    sessionEntity.ManagerID = Int32.Parse(_dr["ManagerID"].ToString());
                    sessionEntity.RoomID = Int32.Parse(_dr["RoomID"].ToString());
                    sessionEntity.CreatedDate = DateTime.Parse(_dr["CreatedDate"].ToString());
                    string modDate = _dr["ModifiedDate"].ToString();
                    if (!string.IsNullOrEmpty(modDate))
                    {
                        sessionEntity.ModifiedDate = DateTime.Parse(modDate);
                    }
                    sessionEntity.Status = Int32.Parse(_dr["Status"].ToString());
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Session] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return sessionEntity;
        }
    }
}